﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Globalization;
using SBS;
using System.Data.SqlClient;

namespace Data
{
    class Ordre_DB
    {
        public static string ConnectionString
        {
            get { return @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SBSdb.mdf;Integrated Security=True;User Instance=True"; }
        }

        public static List<KeyValuePair<string, string>> HentUdlejningsStatus(DateTime UdlejningsDato)
        {
            List<KeyValuePair<string, string>> LedigeScootere = new List<KeyValuePair<string,string>>();

            string sqlScootere = "SELECT Regnr,Certnavn FROM Scooter_Cert WHERE Knr = 1";
            string sqlUdlejning = "SELECT Regnr FROM Udlejning WHERE Ustartdato <=  (CONVERT(DATETIME,'" + UdlejningsDato.Date.ToString() + "', 104)) AND Uslutdato >= (CONVERT(DATETIME,'" + UdlejningsDato.Date.ToString() + "', 104))";

            SqlConnection conn = new SqlConnection(ConnectionString);


            try
            {
                conn.Open();
                SqlCommand com = new SqlCommand(sqlScootere, conn);

                SqlDataReader Sreader = com.ExecuteReader();

                while (Sreader.Read())
                {
                    LedigeScootere.Add(new KeyValuePair<string, string>(Sreader[0].ToString(), Sreader[1].ToString()));
                }
                Sreader.Close();

                com = new SqlCommand(sqlUdlejning, conn);
                SqlDataReader Ureader = com.ExecuteReader();

                while (Ureader.Read())
                {
                    LedigeScootere.RemoveAll(item => item.Key.Equals(Ureader[0].ToString()));
                    
                }

                return LedigeScootere;


            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                conn.Close();
            }





        }

        public static Scooter HentScooter(string RegNr)
        {
                     
            string sql = "SELECT * FROM Scooter_Cert WHERE Regnr = '"+RegNr+"'";

            SqlConnection conn = new SqlConnection(ConnectionString);

            try
            {
                conn.Open();
                SqlCommand com = new SqlCommand(sql,conn);

                SqlDataReader reader = com.ExecuteReader();

                reader.Read();
                
                    return new Scooter(reader[0].ToString(),reader[5].ToString(),int.Parse(reader[4].ToString()),int.Parse(reader[2].ToString()),double.Parse(reader[3].ToString()));

                

            }

            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }


        }
        /// <summary>
        /// Gemmer en ny ordre i databasen
        /// </summary>
        /// <param name="AktivOrdre"></param>
        /// <param name="AktivKunde"></param>
        /// <returns></returns>
        public static bool GemOrdre(Ordre AktivOrdre, bool KontantSalg)
        {
            string sql;
            //Til at indsætte i Ordre tabellen
            if (KontantSalg)
            {
                sql = "INSERT INTO Ordre (Oprettelsedato,Udforsdato,Knr,Regnr,Udfort,Meknr,Faktureret,Betalt) VALUES(CONVERT(DATETIME, '" + DateTime.Now.ToShortDateString() + "',104),CONVERT(DATETIME, '" + DateTime.Now.ToShortDateString() + "',104)," + AktivOrdre.AktuelKunde.Knr + ",'" + AktivOrdre.KundesScooter.RegNr + "','True'," + AktivOrdre.UdfoerendeMekaniker.MekNr + ",'True','True')";
            }

            else
            {
                sql = "INSERT INTO InsertOrdre VALUES(CONVERT(DATETIME, '" + DateTime.Now.ToShortDateString() + "',104),CONVERT(DATETIME, '" + AktivOrdre.UdforsDato.ToShortDateString() + "',104)," + AktivOrdre.AktuelKunde.Knr + ",'" + AktivOrdre.KundesScooter.RegNr + "'," + AktivOrdre.UdfoerendeMekaniker.MekNr + ")";
            }
            //Henter Onr fra ordren
            string sqlHentOnr = "SELECT TOP (1) Onr FROM Ordre ORDER BY Onr DESC";

            SqlConnection conn = new SqlConnection(ConnectionString);

            conn.Open();
            try
            {
                SqlCommand com = new SqlCommand(sql, conn);

                com.ExecuteNonQuery();

                com = new SqlCommand(sqlHentOnr, conn);
                SqlDataReader reader = com.ExecuteReader();
                reader.Read();
                int Onr = (int)reader[0];
                reader.Close();

                //Løkke som smider alle ordrelinierne i databasen
                foreach (OrdreLinie ol in AktivOrdre.OrdreLinier)
                {
                    if (KontantSalg)
                    {
                        sql = "INSERT INTO Ordrelinie (Solgttilpris,Antal,Udfortdato,Udfort,Vnr,Onr) VALUES(" + ol.SolgtTilPris + "," + ol.Antal + ",CONVERT(DATETIME,'" + DateTime.Now.ToString() + "',104)','True'," + ol.VareIOrdreLinien.VareNr + "," + Onr + ")";
                    }
                    else
                    {

                        sql = "INSERT INTO InsertOrdreLinie VALUES(" + ol.SolgtTilPris + "," + ol.Antal + ",'False'," + ol.VareIOrdreLinien.VareNr + "," + Onr + ")";
                    }
                    com = new SqlCommand(sql, conn);
                    com.ExecuteNonQuery();

                }
                //Hvis der er en udlejning tilknyttet ordren Gemmes denne også
                if (AktivOrdre.Udlejning != null)
                {
                    sql = "INSERT INTO Udlejning (Ustartdato,Regnr,Onr) VALUES(CONVERT(DATETIME, '" + AktivOrdre.UdforsDato.ToShortDateString() + "',104),'" + AktivOrdre.Udlejning.Scooter.RegNr + "'," + Onr + ")";
                    com = new SqlCommand(sql, conn);
                    com.ExecuteNonQuery();
                }
                //Hvis alt går godt returneres true
                return true;

                

            }

            catch
            {
                return false;
            }

            finally
            {
                conn.Close();
            }




        }

        public static Dictionary<int, Ordre> HentArbejdsOversigt(int MekNr)
        {
            string sql = "SELECT * FROM Ordre_kunde_scooter WHERE Meknr = " + MekNr +" AND Udforsdato <= CONVERT(DATETIME,'"+DateTime.Today.ToString()+"',104) AND Udfort = 'False'";

            Dictionary<int,Ordre> ArbejdsOversigt = new Dictionary<int,Ordre>();
            

            SqlConnection conn = new SqlConnection(ConnectionString);

            conn.Open();

            try
            {
                SqlCommand com = new SqlCommand(sql, conn);

                SqlDataReader reader = com.ExecuteReader();

                while (reader.Read())
                {
                    Scooter s = new Scooter(reader[11].ToString(), reader[17].ToString(), int.Parse(reader[15].ToString()), int.Parse(reader[13].ToString()), double.Parse(reader[14].ToString()));
                    Kunde k = new Kunde(int.Parse(reader[0].ToString()), reader[1].ToString(), reader[2].ToString(), reader[4].ToString(), reader[3].ToString(), reader[18].ToString(), int.Parse(reader[5].ToString()), reader[6].ToString());

                    Ordre o = new Ordre(int.Parse(reader[7].ToString()), DateTime.Parse(reader[8].ToString()), DateTime.Parse(reader[9].ToString()), k, s);

                    ArbejdsOversigt.Add(o.Onr, o);


                }
                reader.Close();
                //Finder alle ordrelinierne til en Ordre og knytter dem til ordren

                foreach (KeyValuePair<int, Ordre> o in ArbejdsOversigt)
                {
                    o.Value.OrdreLinier = new List<OrdreLinie>();
                    sql = "SELECT * FROM Ordrelinie o INNER JOIN Varetype v ON o.Vnr = v.Vnr WHERE Onr = " + o.Key;

                    com = new SqlCommand(sql, conn);

                    reader = com.ExecuteReader();
                    
                    while (reader.Read())
                    {
                        OrdreLinie ol;
                        
                        //Reservedel
                        if (reader.IsDBNull(10))
                        {

                            ol = new OrdreLinie(new Reservedel(int.Parse(reader[7].ToString()), reader[8].ToString(), double.Parse(reader[9].ToString()), reader[11].ToString()), int.Parse(reader[0].ToString()), int.Parse(reader[2].ToString()), double.Parse(reader[1].ToString()),Convert.ToBoolean(reader[4]));
                        }

                        else
                        {
                            ol = new OrdreLinie(new Ydelse(int.Parse(reader[7].ToString()), reader[8].ToString(), double.Parse(reader[9].ToString()), int.Parse(reader[10].ToString())), int.Parse(reader[0].ToString()), int.Parse(reader[2].ToString()), double.Parse(reader[1].ToString()), Convert.ToBoolean(reader[4]));

                        }

                        o.Value.OrdreLinier.Add(ol); 

                    }
                    
                    reader.Close();

                }

                return ArbejdsOversigt;



            }

            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                conn.Close();
            }



           

        }
       

        public static Dictionary<int, Ordre> HentFaktura()
        {
            string sql = "SELECT * FROM Ordre o Inner Join Kunde k ON o.Knr = k.Knr Inner Join Postnr_by p ON k.Kpostnr = p.Postnr WHERE o.Udfort = 'true'";
            Dictionary<int, Ordre> HentFaktura = new Dictionary<int, Ordre>();
            SqlConnection conn = new SqlConnection(ConnectionString);

            conn.Open();

            try
            {
                SqlCommand com = new SqlCommand(sql, conn);

                SqlDataReader reader = com.ExecuteReader();

                while (reader.Read())
                {
                    Kunde k = new Kunde(int.Parse(reader[9].ToString()), reader[10].ToString(), reader[11].ToString(), reader[13].ToString(), reader[12].ToString(), reader[17].ToString(), int.Parse(reader[14].ToString()), reader[15].ToString());

                    Ordre o = new Ordre(int.Parse(reader[0].ToString()), DateTime.Parse(reader[1].ToString()), DateTime.Parse(reader[2].ToString()), reader[4].ToString(), bool.Parse(reader[7].ToString()), bool.Parse(reader[8].ToString()), k);

                    HentFaktura.Add(o.Onr, o);
                }
                reader.Close();

                foreach (KeyValuePair<int, Ordre> o in HentFaktura)
                {
                    o.Value.OrdreLinier = new List<OrdreLinie>();
                    sql = "SELECT * FROM Ordrelinie o INNER JOIN Varetype v ON o.Vnr = v.Vnr WHERE o.Onr = " + o.Key;

                    com = new SqlCommand(sql, conn);

                    reader = com.ExecuteReader();
                    while (reader.Read())
                    {
                        OrdreLinie ol;

                        //Reservedel
                        if (reader.IsDBNull(10))
                        {

                            ol = new OrdreLinie(new Reservedel(int.Parse(reader[7].ToString()), reader[8].ToString(), double.Parse(reader[9].ToString()), reader[11].ToString()), int.Parse(reader[0].ToString()), int.Parse(reader[2].ToString()), double.Parse(reader[1].ToString()), Convert.ToBoolean(reader[4]),DateTime.Parse(reader[3].ToString()));
                        }
                        //Ydelser
                        else
                        {
                            ol = new OrdreLinie(new Ydelse(int.Parse(reader[7].ToString()), reader[8].ToString(), double.Parse(reader[9].ToString()), int.Parse(reader[10].ToString())), int.Parse(reader[0].ToString()), int.Parse(reader[2].ToString()), double.Parse(reader[1].ToString()), Convert.ToBoolean(reader[4]), DateTime.Parse(reader[3].ToString()));
                        }
                        o.Value.OrdreLinier.Add(ol);
                    }

                    reader.Close();

                }

                
                foreach (KeyValuePair<int, Ordre> o in HentFaktura)
                {
                    sql = "SELECT * FROM Udlejning WHERE Onr = " + o.Key;
                    com = new SqlCommand(sql, conn);
                    reader = com.ExecuteReader();

                    while (reader.Read())
                    {
                        o.Value.Udlejning = new Udlejning(bool.Parse(reader[1].ToString()), double.Parse(reader[2].ToString()), DateTime.Parse(reader[3].ToString()), DateTime.Parse(reader[4].ToString()), reader[5].ToString(), double.Parse(reader[6].ToString()), int.Parse(reader[7].ToString()), int.Parse(reader[8].ToString()), reader[9].ToString(), int.Parse(reader[10].ToString()));
                    }
                    reader.Close();
                    

                }

                return HentFaktura;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Opdaterer ordre info om hvad der er blevet udført etc
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        public static bool RegistrerArbejde(Ordre o)
        {
            string sql = "UPDATE Ordre SET Udfort = '" + o.Udfort.ToString() + "' WHERE Onr = " + o.Onr;
            string sqlOpdaterScooter = "UPDATE Scooter SET Sidstservice = CONVERT(DATETIME,'"+DateTime.Now.ToShortDateString()+"',104), Antkm = "+o.KundesScooter.AntKm+" WHERE Regnr = '"+o.KundesScooter.RegNr+"'"; 

            SqlConnection conn = new SqlConnection(ConnectionString);

            try
            {
                conn.Open();

                SqlCommand com = new SqlCommand(sql, conn);

                com.ExecuteNonQuery();

                com = new SqlCommand(sqlOpdaterScooter,conn);
                com.ExecuteNonQuery();

                
                foreach (OrdreLinie ol in o.OrdreLinier)
                {
                    //Tilføjer alle nye varer til OrdreLinieTabellen
                    if (ol.Olnr == 0)
                    {
                        sql = "INSERT into Ordrelinie (Solgttilpris,Antal,Udfortdato,Udfort,Vnr,Onr) VALUES(" + ol.SolgtTilPris + "," + ol.Antal + ",CONVERT(DATETIME,'" +ol.UdfortDatoTid.ToString() + "',104),'" + ol.Udfort.ToString() + "'," + ol.VareIOrdreLinien.VareNr + "," + o.Onr+")";
                        com = new SqlCommand(sql, conn);
                        com.ExecuteNonQuery();

                    }
                        //opdaterer alle eksisterende ordrelinier
                    else
                    {
                        sql = "UPDATE Ordrelinie SET Udfort = '" + ol.Udfort.ToString() + "', Udfortdato = CONVERT(DATETIME,'" + ol.UdfortDatoTid.ToString() + "',104) WHERE Olnr = " + ol.Olnr;
                        com = new SqlCommand(sql, conn);
                        com.ExecuteNonQuery();

                    }
                }

                return true;

            }

            catch
            {
                return false;

            }
            finally
            {
                conn.Close();
            }

        }
        /// <summary>
        /// Opdaterer alle ordrer, fra at være ufaktureret til faktureret
        /// </summary>
        /// <param name="Ordrer">Dictionary indeholdende ordreobjekter med Onr som Key</param>
        /// <returns>Sand eller falsk afhængig af succes</returns>
        public static bool OpdaterFaktureringsStatus(Dictionary<int, SBS.Ordre> Ordrer)
        {

            var IkkeFaktureret = from f in Ordrer where f.Value.Faktureret == false select f;

            SqlConnection conn = new SqlConnection(ConnectionString);

            SqlCommand com;
            try
            {
                conn.Open();
                

                foreach (var v in IkkeFaktureret)
                {
                    string sql = "UPDATE Ordre SET Faktureret = 'True' WHERE Onr = " + v.Key;
                    com = new SqlCommand(sql, conn);
                    com.ExecuteNonQuery();




                }
                return true;
            }

            catch
            {
                return false;
                
            }

            finally
            {
                conn.Close();
            }

        }


       /// <summary>
        /// Opdaterer betalingsstatus på en Ordre
       /// </summary>
       /// <param name="Onr">Ordre nummer</param>
       /// <returns>Sandt eller falsk afhængig af succes</returns>
        public static bool OpdaterBetalingsStatus(int Onr)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);

            
            try
            {
                conn.Open();
                string sql = "UPDATE Ordre SET Betalt = 'True' WHERE Onr = " + Onr;
                SqlCommand com = new SqlCommand(sql, conn);
                com.ExecuteNonQuery();
                
                return true;
            }

            catch
            {
                return false;

            }

            finally
            {
                conn.Close();
            }

        }

        public static Udlejning HentUdlejning(int Onr)
        {
            string sql = "SELECT u.Udlejet,u.Selvrisikobelob,u.Ustartdato,u.Uslutdato,u.korekortnr,u.Prisprkm,u.Startkortkm,u.Slutkortkm, u.Regnr FROM Udlejning u WHERE u.Onr = "+Onr;

            SqlConnection conn = new SqlConnection(ConnectionString);

            try
            {
                conn.Open();

                SqlCommand com = new SqlCommand(sql, conn);

                SqlDataReader reader = com.ExecuteReader();

                if (reader.Read())
                {

                    return new Udlejning(bool.Parse(reader[0].ToString()), double.Parse(reader[1].ToString()), DateTime.Parse(reader[2].ToString()), DateTime.Parse(reader[3].ToString()), reader[4].ToString(), double.Parse(reader[5].ToString()), int.Parse(reader[6].ToString()), int.Parse(reader[7].ToString()),reader[8].ToString(),Onr);
                }
                else
                {
                    return null;
                }

            }

            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                conn.Close();
            }

        }

        public static bool OpdaterUdlejning(Udlejning u)
        {
            string sql = "UPDATE Udlejning SET Udlejet = '" + u.Udlejet + "', Selvrisikobelob = " + u.SelvRisikoBelob.ToString(new CultureInfo("en-US", false)) + ", Ustartdato = CONVERT(DATETIME,'" + u.UdlejetStartDato.ToString() + "',104), Uslutdato = CONVERT(DATETIME,'" + u.UdlejetSlutDato.ToString() + "',104), Korekortnr = '" + u.KorekortNr + "', Prisprkm = " + u.PrisPrKm.ToString(new CultureInfo("en-US", false)) + ", Startkortkm = " + u.StartKortKm + " , Slutkortkm = " + u.SlutKortKm + " WHERE Onr = " + u.Onr;

            SqlConnection conn = new SqlConnection(ConnectionString);

            try
            {
                conn.Open();

                SqlCommand com = new SqlCommand(sql, conn);

                com.ExecuteNonQuery();



                return true;
            }

            catch
            {
                return false;
            }

            finally
            {
                conn.Close();
            }

        }

        public static bool SletUdlejning(Udlejning u)
        {
            string sql = "DELETE FROM Udlejning WHERE Onr = " + u.Onr;

            SqlConnection conn = new SqlConnection(ConnectionString);

            try
            {
                conn.Open();

                SqlCommand com = new SqlCommand(sql, conn);

                com.ExecuteNonQuery();
                
                return true;
            }

            catch
            {
                return false;
            }

            finally
            {
                conn.Close();
            }


        }
         
    }
}
